﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;

public partial class home_Work : System.Web.UI.Page
{
    int catalogId = 0;
    int projectId = 0;
    int page_current = 1;
    int userId = 0;
    public Boolean rightUser = true;
    public Boolean isLeader = true;
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["id_user"] == null || Session["permission"] == null || ((int)Session["permission"]) <= 1)
            Response.Redirect("../login.aspx");

        #region get catalalog and project
        // get catalog
        try
        {
            catalogId = Int32.Parse(Request.QueryString["catalog"]);
        }
        catch
        {
            catalogId = 0;
        }

        // get project
        if (catalogId == 0)
        {
            try
            {
                projectId = Int32.Parse(Request.QueryString["project"]);
            }
            catch
            {
                projectId = 0;
            }

        }
        #endregion

        if (catalogId == 0 && projectId == 0)
            return;
        #region get userId
        try
        {
            userId = Int32.Parse(Request.QueryString["user"]);
        }
        catch
        {
            userId = 0;
        }
        #endregion

        #region get page_current
        try
        {
            page_current = Int32.Parse(Request.QueryString["page"]);
        }
        catch
        {
            page_current = 1;
        }
    
        #endregion

        if (!IsPostBack)
        {
            string strOrderby = orderby.SelectedValue;
            if (Session["orderby_work"] != null)
            {
                strOrderby = Session["orderby_work"].ToString();
                orderby.Items.FindByValue(orderby.SelectedValue).Selected = false;
                orderby.Items.FindByValue(strOrderby).Selected = true;
            }
            string strOrder = order.SelectedValue;
            if (Session["order_work"] != null)
            {
                strOrder = Session["order_work"].ToString();
                order.Items.FindByValue(order.SelectedValue).Selected = false;
                order.Items.FindByValue(strOrder).Selected = true;
            }
            BindData(strOrderby, strOrder);

            BindData1();
        }
        
    }

    private void BindData(string orderby,string order)
    {
        int page_number = 0;
        DataTable dtWork = null;
        string sql = "";
        int idProject = 0;
        Boolean isPulicProject = false;
        int currentPermission = 3;

        #region check project is public when param in querystring doesn't include userId
        if (catalogId != 0)
        {
            sql = "SELECT projectId FROM [catalog] WHERE id=" + comm.to_sql(catalogId.ToString(), "number");
            DataTable dtTable = dal.SelectTable(sql);
            if (dtTable.Rows.Count == 1)
            {
                idProject = Int32.Parse(dtTable.Rows[0]["projectId"].ToString());
            }
        }
        int currentProject = (projectId == 0) ? idProject : projectId;
        sql = "SELECT [public] FROM project WHERE id=" + comm.to_sql(currentProject.ToString(), "number");
        DataTable dtTable1 = dal.SelectTable(sql);
        if (dtTable1.Rows.Count == 1)
        {
            string strpublic = dtTable1.Rows[0]["public"].ToString();
            if (strpublic.ToLower() == "true")
                isPulicProject = true;
        }
        #endregion

        #region get the permission of current user in this project
        if (((int)Session["permission"]) >= 4) // get the permission in this project
        {
            int tempProject = (projectId == 0) ? idProject : projectId;
            sql = " SELECT permission FROM UserProject " +
                  " WHERE projectId=" + comm.to_sql(tempProject.ToString(), "number") +
                  " AND userId=" + comm.to_sql(Session["id_user"].ToString(), "number");
            DataTable dtTable = dal.SelectTable(sql);
            if (dtTable.Rows.Count == 1)
            {
                currentPermission = Int32.Parse(dtTable.Rows[0]["permission"].ToString());
            }
        }
        #endregion

        if (isPulicProject == false)
        {
            if (((int)Session["permission"]) >= 4 && currentPermission >= 5 && userId != Int32.Parse(Session["id_user"].ToString()))
            {
                return;
            }
        }


        if (catalogId != 0) // get works by catalog
        {
            // check permission to access the project that contains  this catalog
            sql = "SELECT projectId FROM [catalog] WHERE id=" + comm.to_sql(catalogId.ToString(), "number");
            DataTable dtTable = dal.SelectTable(sql);
            if (dtTable.Rows.Count == 1)
            {
                idProject = Int32.Parse(dtTable.Rows[0]["projectId"].ToString());
                
                // check current user is enable to access this catalog
                SqlParameter[] listParams = new SqlParameter[3];
                //-- param 1: @userId
                int id = Int32.Parse(Session["id_user"].ToString());
                SqlParameter param = new SqlParameter("@userId", id);
                param.SqlDbType = SqlDbType.Int;
                param.Direction = ParameterDirection.Input;
                listParams[0] = param;

                //-- param 2: @projectId
                param = new SqlParameter("@projectId", idProject);
                param.SqlDbType = SqlDbType.Int;
                param.Direction = ParameterDirection.Input;
                listParams[1] = param;

                //-- param 3: @isAccess
                param = new SqlParameter();
                param.ParameterName = "@isAccess";
                param.SqlDbType = SqlDbType.Int;
                param.Direction = ParameterDirection.Output;
                listParams[2] = param;

                dal.ExecuteSP("proCheckAccessProject_GET", listParams);
                int enable = Int32.Parse(listParams[2].Value.ToString());
                if (enable == 1)    // enable to access this catalog
                {
                    listParams = new SqlParameter[7];
                    // -- param 1: @userId
                    param = new SqlParameter("@userId", userId);
                    param.Direction = ParameterDirection.Input;
                    param.SqlDbType = SqlDbType.Int;
                    listParams[0] = param;
                    // -- param 2: @catId
                    param = new SqlParameter("@catId", catalogId);
                    param.Direction = ParameterDirection.Input;
                    param.SqlDbType = SqlDbType.Int;
                    listParams[1] = param;
                    // -- param 3: @page_size
                    param = new SqlParameter("@page_size",10);
                    param.Direction = ParameterDirection.Input;
                    param.SqlDbType = SqlDbType.Int;
                    listParams[2] = param;
                    // -- param 4: @page_current
                    param = new SqlParameter("@page_current",page_current);
                    param.Direction = ParameterDirection.Input;
                    param.SqlDbType = SqlDbType.Int;
                    listParams[3] = param;
                    // -- param 5: @orderby
                    param = new SqlParameter("@orderby",orderby);
                    param.Direction = ParameterDirection.Input;
                    param.SqlDbType = SqlDbType.VarChar;
                    param.Size = 50;
                    listParams[4] = param;
                    // -- param 6: @typeorder
                    param = new SqlParameter("@typeorder",order);
                    param.Direction = ParameterDirection.Input;
                    param.SqlDbType = SqlDbType.VarChar;
                    param.Size = 50;
                    listParams[5] = param;
                    // -- param 7: @page_number
                    param = new SqlParameter();
                    param.ParameterName = "@page_number";
                    param.Direction = ParameterDirection.Output;
                    param.SqlDbType = SqlDbType.Int;
                    listParams[6] = param;

                    dtWork = dal.SelectTableSP("proWorkByCat_SEL",listParams);
                    page_number = Int32.Parse(listParams[6].Value.ToString());
                }
            }   
        }
        else if (projectId != 0)    // get works by project
        {
            // check current user is enable to access this project
            SqlParameter[] listParams = new SqlParameter[3];
            //-- param 1: @userId
            int id = Int32.Parse(Session["id_user"].ToString());
            SqlParameter param = new SqlParameter("@userId", id);
            param.SqlDbType = SqlDbType.Int;
            param.Direction = ParameterDirection.Input;
            listParams[0] = param;

            //-- param 2: @projectId
            param = new SqlParameter("@projectId", projectId);
            param.SqlDbType = SqlDbType.Int;
            param.Direction = ParameterDirection.Input;
            listParams[1] = param;

            //-- param 3: @isAccess
            param = new SqlParameter();
            param.ParameterName = "@isAccess";
            param.SqlDbType = SqlDbType.Int;
            param.Direction = ParameterDirection.Output;
            listParams[2] = param;

            dal.ExecuteSP("proCheckAccessProject_GET", listParams);
            int enable = Int32.Parse(listParams[2].Value.ToString());
            if (enable == 1)    // enable to access this project
            {
                listParams = new SqlParameter[7];
                // -- param 1: @userId
                param = new SqlParameter("@userId", userId);
                param.Direction = ParameterDirection.Input;
                param.SqlDbType = SqlDbType.Int;
                listParams[0] = param;
                // -- param 2: @projectId
                param = new SqlParameter("@projectId", projectId);
                param.Direction = ParameterDirection.Input;
                param.SqlDbType = SqlDbType.Int;
                listParams[1] = param;
                // -- param 3: @page_size
                param = new SqlParameter("@page_size", 10);
                param.Direction = ParameterDirection.Input;
                param.SqlDbType = SqlDbType.Int;
                listParams[2] = param;
                // -- param 4: @page_current
                param = new SqlParameter("@page_current", page_current);
                param.Direction = ParameterDirection.Input;
                param.SqlDbType = SqlDbType.Int;
                listParams[3] = param;
                // -- param 5: @orderby
                param = new SqlParameter("@orderby", orderby);
                param.Direction = ParameterDirection.Input;
                param.SqlDbType = SqlDbType.VarChar;
                param.Size = 50;
                listParams[4] = param;
                // -- param 6: @typeorder
                param = new SqlParameter("@typeorder", order);
                param.Direction = ParameterDirection.Input;
                param.SqlDbType = SqlDbType.VarChar;
                param.Size = 50;
                listParams[5] = param;
                // -- param 7: @page_number
                param = new SqlParameter();
                param.ParameterName = "@page_number";
                param.Direction = ParameterDirection.Output;
                param.SqlDbType = SqlDbType.Int;
                listParams[6] = param;

                dtWork = dal.SelectTableSP("proWorkByProject_SEL", listParams);
                page_number = Int32.Parse(listParams[6].Value.ToString());
            }
        }

        // check permission to add/edit this works in current project
        if (currentPermission >= 5 && ((int)Session["permission"]) >= 4) // if current user is normal user
        {
            if (userId != 0)    // current user views works which are taked by them
            {
                if (userId == Int32.Parse(Session["id_user"].ToString()))
                {
                    works.Columns[9].Visible = true;
                    rightUser = true;
                }
                else
                {
                    rightUser = false;
                    works.Columns[9].Visible = false;
                }
            }
            else
                works.Columns[9].Visible = false;
            menu.Visible = false;
            menu.Enabled = false;
            isLeader = false;
        }
        else
        {
            works.Columns[9].Visible = true;
            menu.Visible = true;
            menu.Enabled = true;
            isLeader = true;
            rightUser = true;
        }

        works.DataSource = dtWork;
        works.DataBind();

        #region Display pager
        if (page_number == 0)
        {
            display.Text = "Chưa có công việc nào";
            pager.Visible = false;
            this.order.Visible = false;
            this.orderby.Visible = false;
            this.label1.Visible = false;
            this.label2.Visible = false;
        }
        else if (page_number == 1)
        {
            pager.Visible = false;
            display.Text = "";
        }
        else if (page_number > 1)
        {
            display.Text = "";
            // load pager
            pager.link = "~/home/work.aspx";
            pager.numberpage = page_number;
            pager.currentpage = page_current;
            if (catalogId != 0)
            {
                if (userId != 0)
                {
                    pager.moreParamName = new string[2];
                    pager.moreParamValue = new string[2];
                    pager.moreParamName[0] = "catalog";
                    pager.moreParamName[1] = "user";
                    pager.moreParamValue[0] = catalogId.ToString();
                    pager.moreParamValue[1] = userId.ToString();
                }
                else
                {
                    pager.moreParamName = new string[1];
                    pager.moreParamValue = new string[1];
                    pager.moreParamName[0] = "catalog";
                    pager.moreParamValue[0] = catalogId.ToString();
                }
            }
            else if (projectId != 0)
            {

                if (userId != 0)
                {
                    pager.moreParamName = new string[2];
                    pager.moreParamValue = new string[2];
                    pager.moreParamName[0] = "project";
                    pager.moreParamName[1] = "user";
                    pager.moreParamValue[0] = projectId.ToString();
                    pager.moreParamValue[1] = userId.ToString();
                }
                else
                {
                    pager.moreParamName = new string[1];
                    pager.moreParamValue = new string[1];
                    pager.moreParamName[0] = "project";
                    pager.moreParamValue[0] = projectId.ToString();
                }
            }
            pager.paramPageName = "page";
            pager.Visible = true;
            pager.BindData();
        }
        #endregion
    }

    private void BindData1()
    {
        #region load list of catalogs
        string sql = "";
        if (catalogId != 0)
        {
            sql = " SELECT id,[name] FROM [catalog] " +
                  " WHERE projectId IN (SELECT projectId FROM [catalog] WHERE id= " + comm.to_sql(catalogId.ToString(), "number") + ")";
            DataTable dtTable = dal.SelectTable(sql);
            if (dtTable.Rows.Count >= 0)
            {
                foreach (DataRow row in dtTable.Rows)
                {
                    ListItem item = new ListItem(row["name"].ToString(), row["id"].ToString());
                    catalog_work.Items.Add(item);
                }
                catalog_work.Items.FindByValue(catalogId.ToString()).Selected = true;
            }
        }
        else if (projectId != 0)
        {
            sql = " SELECT id,[name] FROM [catalog] " +
                  " WHERE projectId= " + comm.to_sql(projectId.ToString(), "number");
            DataTable dtTable = dal.SelectTable(sql);
            if (dtTable.Rows.Count >= 0)
            {
                foreach (DataRow row in dtTable.Rows)
                {
                    ListItem item = new ListItem(row["name"].ToString(), row["id"].ToString());
                    catalog_work.Items.Add(item);
                }
            }
        }
        else
        {
            Response.Redirect("~/home/default.aspx");
        }
        #endregion


        #region load list prioritys
        priority_work.Attributes["style"] = "margin:0;";
        ListItem liItem = new ListItem(DisplayEnum.DisplayPriority((int)Priority.normal), ((int)Priority.normal).ToString());
        liItem.Selected = true;
        priority_work.Items.Add(liItem);

        liItem = new ListItem(DisplayEnum.DisplayPriority((int)Priority.priority), ((int)Priority.priority).ToString());
        priority_work.Items.Add(liItem);

        liItem = new ListItem(DisplayEnum.DisplayPriority((int)Priority.hurry), ((int)Priority.hurry).ToString());
        priority_work.Items.Add(liItem);

        liItem = new ListItem(DisplayEnum.DisplayPriority((int)Priority.urgent), ((int)Priority.urgent).ToString());
        priority_work.Items.Add(liItem);
        #endregion
        

        #region load list of usernames
        if (catalogId != 0)
        {
            sql = "SELECT projectId FROM [catalog] WHERE id=" + comm.to_sql(catalogId.ToString(), "number");
            DataTable dtTable = dal.SelectTable(sql);
            if (dtTable.Rows.Count == 1)
            {
                sql = " SELECT id,username FROM [user],UserProject " +
                      " WHERE [user].id=UserProject.userId " +
                      " AND UserProject.projectId=" + comm.to_sql(dtTable.Rows[0]["projectId"].ToString(), "number");
                dtTable = dal.SelectTable(sql);
                if (dtTable.Rows.Count >= 1)
                {
                    foreach (DataRow row in dtTable.Rows)
                    {
                        ListItem item = new ListItem(row["username"].ToString(), row["id"].ToString());
                        username_work.Items.Add(item);
                    }
                }
            }
        }
        else if (projectId != 0)
        {
            sql = " SELECT id,username FROM [user],UserProject " +
                     " WHERE [user].id=UserProject.userId " +
                     " AND UserProject.projectId=" + comm.to_sql(projectId.ToString(), "number");
            DataTable dtTable = dal.SelectTable(sql);
            if (dtTable.Rows.Count >= 1)
            {
                foreach (DataRow row in dtTable.Rows)
                {
                    ListItem item = new ListItem(row["username"].ToString(), row["id"].ToString());
                    username_work.Items.Add(item);
                }
            }
        }
        #endregion

        #region load list of status
        status_work.Attributes["style"] = "margin:0;";
        liItem = new ListItem(DisplayEnum.DisplayStatusVN((int)Status.unrealized), ((int)Status.unrealized).ToString());
        liItem.Selected = true;
        status_work.Items.Add(liItem);

        liItem = new ListItem(DisplayEnum.DisplayStatusVN((int)Status.doing), ((int)Status.doing).ToString());
        status_work.Items.Add(liItem);
        
        liItem = new ListItem(DisplayEnum.DisplayStatusVN((int)Status.done), ((int)Status.done).ToString());
        status_work.Items.Add(liItem);
        #endregion

    }

    protected void orderby_SelectedIndexChanged(object sender, EventArgs e)
    {
        string strOrderby = ((DropDownList)sender).SelectedValue;
        Session.Add("orderby_work", strOrderby);
        Session.Timeout = 60;
        string strOrder = order.SelectedValue;
        BindData(strOrderby, strOrder);
    }

    protected void order_SelectedIndexChanged(object sender, EventArgs e)
    {
        string strOrder = ((DropDownList)sender).SelectedValue;
        Session.Add("order_work", strOrder);
        Session.Timeout = 60;
        string strOrderby = orderby.SelectedValue;
        BindData(strOrderby, strOrder);
    }

    protected void btOk_Click(object sender,EventArgs e)
    {
        string strOrderby = (Session["orderby_work"] != null ? Session["orderby_work"].ToString() : orderby.SelectedValue);
        string strOrder = (Session["order_work"] != null ? Session["order_work"].ToString() : order.SelectedValue);
        BindData(strOrderby, strOrder);
    }

    protected void works_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "removeWork")
        {
            // get catalog and project of current work which will be deleted
            string sql = "SELECT projectId,catId FROM work WHERE id=" + comm.to_sql(e.CommandArgument.ToString(), "number");
            DataTable dtTable = dal.SelectTable(sql);
            int projectId = 0;
            int catId = 0;
            if (dtTable.Rows.Count == 1)
            {
                projectId = Int32.Parse(dtTable.Rows[0]["projectId"].ToString());
                catId = Int32.Parse(dtTable.Rows[0]["catId"].ToString());
            }

            // delete work----------------------------------------------------
            // get note , delete attach and note
            sql = "SELECT id FROM note Where workId=" + comm.to_sql(e.CommandArgument.ToString(), "number");
            dtTable = dal.SelectTable(sql);
            if (dtTable.Rows.Count >= 1)
            {
                foreach (DataRow row in dtTable.Rows)
                {
                    // delete attach
                    sql = "DELETE FROM attach WHERE noteId=" + comm.to_sql(row["id"].ToString(), "number");
                    dal.Execute(sql);
                    // delete note
                    sql = "DELETE FROM note WHERE id=" + comm.to_sql(row["id"].ToString(), "number");
                    dal.Execute(sql);
                }
            }
            // get userId of current work before deleting it------------------------
            int idUser = 0;
            int idLeader = 0;
            string content = "";
            sql = "SELECT userId,leaderId,[content] FROM work WHERE id =" + comm.to_sql(e.CommandArgument.ToString(), "number");
            dtTable = dal.SelectTable(sql);
            if (dtTable.Rows.Count == 1)
            {
                idUser = Int32.Parse(dtTable.Rows[0]["userId"].ToString());
                idLeader = Int32.Parse(dtTable.Rows[0]["leaderId"].ToString());
                content = dtTable.Rows[0]["content"].ToString();
            }

            // delete work---------------------------------------------------------
            sql = "DELETE FROM work WHERE id=" + comm.to_sql(e.CommandArgument.ToString(), "number");
            dal.Execute(sql);

            // update completepercent of catalog and project-------------------
            SqlParameter[] someParams = new SqlParameter[2];
            //-- param 1: @catId

            SqlParameter param = new SqlParameter("@catId", catId);
            param.SqlDbType = SqlDbType.Int;
            param.Direction = ParameterDirection.Input;
            someParams[0] = param;

            //-- param 2: @projectId
            param = new SqlParameter("@projectId", projectId);
            param.SqlDbType = SqlDbType.Int;
            param.Direction = ParameterDirection.Input;
            someParams[1] = param;
            dal.ExecuteSP("proUpdatePercentProjectCat_EXE", someParams);

            

            #region send mail ----------------------------------------------------------------
            // send mail cho user
            if (idUser != userId && idUser != 0)
            {
                sql = "SELECT email,mailwhenwork FROM [user] WHERE id=" + comm.to_sql(idUser.ToString(), "number");
                dtTable = dal.SelectTable(sql);
                if (dtTable.Rows.Count == 1)
                {
                    string email = dtTable.Rows[0]["email"].ToString();
                    string mailwhenwork = dtTable.Rows[0]["mailwhenwork"].ToString().ToLower();
                    string mailcontent = "Công việc \" " + content + " \" vừa được xóa ! Hãy vào http://localhost/workmanagement để kiểm tra !";
                    if (mailwhenwork == "true")
                    {
                        comm.SendMail("idt2010.vn@gmail.com", email, "Xóa công việc", mailcontent);
                    }
                }
            }

            // send mail cho leader
            if (idLeader != userId && idLeader != 0)
            {
                sql = "SELECT email,mailwhenwork FROM [user] WHERE id=" + comm.to_sql(idLeader.ToString(), "number");
                dtTable = dal.SelectTable(sql);
                if (dtTable.Rows.Count == 1)
                {
                    string email = dtTable.Rows[0]["email"].ToString();
                    string mailwhenwork = dtTable.Rows[0]["mailwhenwork"].ToString().ToLower();
                    string mailcontent = "Công việc \" " + content + " \" vừa được xóa ! Hãy vào http://localhost/workmanagement để kiểm tra !";
                    if (mailwhenwork == "true")
                    {
                        comm.SendMail("idt2010.vn@gmail.com", email, "Xóa công việc", mailcontent);
                    }
                }
            }
            #endregion
            //string strOrderby = (Session["orderby_work"] != null ? Session["orderby_work"].ToString() : orderby.SelectedValue);
            //string strOrder = (Session["order_work"] != null ? Session["order_work"].ToString() : order.SelectedValue);
            //BindData(strOrderby, strOrder);
            Response.Redirect(Request.RawUrl);
        }
    }

    protected void btNote_Click(object sender, EventArgs e)
    {
        string note = comm.StandardString(content_note.Text);
        if (note.Length == 0)
            return;

        string strValue1 = System.IO.Path.GetFileName(fileupload1.PostedFile.FileName);
        string strValue2 = System.IO.Path.GetFileName(fileupload2.PostedFile.FileName);
        string sPath = "";
        string sYear = comm.GetCurrentYear();
        string sMonth = comm.GetCurrentMonth();
        string sDay = comm.GetCurrentDay();
        string sHour = comm.GetCurrentHour();
        string sMinute = comm.GetCurrentMinute();
        sPath = "/Files/Uploads/" + sYear + "/" + sMonth + "/" + sDay + "/" + sHour + "/" + sMinute;
        string SaveLocation1 = sPath + "/" + strValue1;
        string SaveLocation2 = sPath + "/" + strValue2;
        sPath = Server.MapPath("~\\Files\\Uploads") + "\\" + sYear + "\\" + sMonth + "\\" + sDay 
                                + "\\" + sHour + "\\" + sMinute;
        string SaveLocationPhysical1 = sPath + "\\" + strValue1;
        string SaveLocationPhysical2 = sPath + "\\" + strValue2;
        Boolean isUpload1 = false;
        Boolean isUpload2 = false;

        if (fileupload1.PostedFile.ContentLength > 0)
        {
            // Get the size in bytes of the file to upload.
            int fileSize = fileupload1.PostedFile.ContentLength;

            // Allow only files less than _fileSize bytes and have extension is .jpg to be uploaded.
            if ((fileSize <= 2000 * 1024))
            {
                if (!Directory.Exists(sPath))
                    Directory.CreateDirectory(sPath);
                // Get the name of the file to upload.
                string fileName = Server.HtmlEncode(fileupload1.PostedFile.FileName);
                fileupload1.PostedFile.SaveAs(SaveLocationPhysical1);
                isUpload1 = true;
            }
        }

        if (fileupload2.PostedFile.ContentLength > 0)
        {
            // Get the size in bytes of the file to upload.
            int fileSize = fileupload1.PostedFile.ContentLength;

            // Allow only files less than _fileSize bytes and have extension is .jpg to be uploaded.
            if ((fileSize <= 2000 * 1024))
            {
                if (!Directory.Exists(sPath))
                    Directory.CreateDirectory(sPath);
                // Get the name of the file to upload.
                string fileName = Server.HtmlEncode(fileupload2.PostedFile.FileName);
                fileupload2.PostedFile.SaveAs(SaveLocationPhysical2);
                isUpload2 = true;
            }
        }

        // add a new comment-------------------------
        int userId = Int32.Parse(Session["id_user"].ToString());
        note = note.Replace("\n", "<br/>");
        string date = comm.GetCurrentDay() + "/" + comm.GetCurrentMonth() + "/" + comm.GetCurrentYear();
        string sql = " INSERT INTO note(userId,workId,[content],date)" +
            " VALUES(" + comm.to_sql(userId.ToString(), "number") + "," + comm.to_sql(workId.Value, "number") +
            "," + comm.to_sql(note, "string") + "," + comm.to_sql(date, "date") + ")";
        if (dal.Execute(sql) != 1)
            return;
        sql = " SELECT id FROM note WHERE userId=" + comm.to_sql(userId.ToString(), "number") +
            " AND workId=" + comm.to_sql(workId.Value, "number") +
            " AND CAST([content] AS NVARCHAR(MAX))=" + comm.to_sql(note, "string");
        
        int noteId = Int32.Parse(dal.SelectTable(sql).Rows[0]["id"].ToString());
        
        // add attachs ------------------------------------
        if (isUpload1)
        {
            string name = fileupload1.PostedFile.FileName;
            sql = " INSERT INTO attach([name],link,noteId) " +
                  " VALUES(" + comm.to_sql(name, "string") + "," + comm.to_sql(SaveLocation1, "string") + "," +
                  comm.to_sql(noteId.ToString(), "number") + ")";
            dal.Execute(sql);
            fileupload1.Dispose();
        }
        if (isUpload2)
        {
            string name = fileupload2.PostedFile.FileName;
            sql = " INSERT INTO attach([name],link,noteId) " +
                  " VALUES(" + comm.to_sql(name, "string") + "," + comm.to_sql(SaveLocation2, "string") + "," +
                  comm.to_sql(noteId.ToString(), "number") + ")";
            dal.Execute(sql);
            fileupload2.Dispose();
        }

        //string strOrderby = (Session["orderby_work"] != null ? Session["orderby_work"].ToString() : orderby.SelectedValue);
        //string strOrder = (Session["order_work"] != null ? Session["order_work"].ToString() : order.SelectedValue);
        //BindData(strOrderby, strOrder);

        #region send mail ----------------------------------------------------------------
        // get userId of current work
        int idUser = 0;
        int idLeader = 0;
        string content = "";
        sql = "SELECT userId,leaderId,[content] FROM work WHERE id =" + comm.to_sql(workId.Value, "number");
        DataTable dtTable = dal.SelectTable(sql);
        if (dtTable.Rows.Count == 1)
        {
            idUser = Int32.Parse(dtTable.Rows[0]["userId"].ToString());
            idLeader = Int32.Parse(dtTable.Rows[0]["leaderId"].ToString());
            content = dtTable.Rows[0]["content"].ToString();
        }
        // send mail cho user
        if (idUser != userId && idUser != 0)
        {
            sql = "SELECT email,mailwhencomment FROM [user] WHERE id=" + comm.to_sql(idUser.ToString(), "number");
            dtTable = dal.SelectTable(sql);
            if (dtTable.Rows.Count == 1)
            {
                string email = dtTable.Rows[0]["email"].ToString();
                string mailwhencomment = dtTable.Rows[0]["mailwhencomment"].ToString().ToLower();
                string mailcontent = "Công việc \" " + content + " \" vừa được chú thích ! Hãy vào http://localhost/workmanagement để kiểm tra !";
                if (mailwhencomment == "true")
                {
                    comm.SendMail("idt2010.vn@gmail.com", email, "Chú thích công việc", mailcontent);
                }
            }

        }

        // send mail cho leader
        if (idLeader != userId && idLeader != 0)
        {
            sql = "SELECT email,mailwhencomment FROM [user] WHERE id=" + comm.to_sql(idLeader.ToString(), "number");
            dtTable = dal.SelectTable(sql);
            if (dtTable.Rows.Count == 1)
            {
                string email = dtTable.Rows[0]["email"].ToString();
                string mailwhencomment = dtTable.Rows[0]["mailwhencomment"].ToString().ToLower();
                string mailcontent = "Công việc \" " + content + " \" vừa được chú thích ! Hãy vào http://localhost/workmanagement để kiểm tra !";
                if (mailwhencomment == "true")
                {
                    comm.SendMail("idt2010.vn@gmail.com", email, "Chú thích công việc", mailcontent);
                }
            }
        }
        #endregion
        Response.Redirect(Request.RawUrl);
    }
}
